<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

Class Mindex extends MY_Model {

    public function __construct() {
        parent::__construct();
    }

    public function getListNetworkGroup() {
        if ($this->session->userdata('level') == 2) {
            $sql = 'SELECT tg.id, tg.name FROM tbl_group AS tg LEFT JOIN manage_group_key AS gk ON gk.group_id = tg.id WHERE gk.user_id = ' . $this->session->userdata('id');
        } else {
            $sql = "SELECT id, name FROM tbl_group ORDER BY id ASC";
        }
        $data = $this->db->query($sql)->result_array();

        $arr = array();
        $arr[0] = 'Chọn nhóm mạng';
        foreach ($data AS $el => $le) {
            $arr[$le['id']] = $le['name'];
        }
        return $arr;
    }

    public function getNetworkInGroup($id = 0) {
        if (!in_array($id, array(null, '', '0'))) {
            $province = $this->db->query('SELECT DISTINCT p.provinceid, p.name FROM province AS p LEFT JOIN tbl_address AS ta ON ta.id_province = p.provinceid INNER JOIN tbl_device AS td ON td.id_address = ta.id LEFT JOIN tbl_group AS tg ON tg.id = td.network_group WHERE tg.id = ' . $id)->result_object();

            foreach ($province AS $key => $value) {
                $district = $this->db->query('SELECT DISTINCT d.districtid, d.name FROM district AS d LEFT JOIN tbl_address AS ta ON ta.id_district = d.districtid INNER JOIN tbl_device AS td ON td.id_address = ta.id LEFT JOIN tbl_group AS tg ON tg.id = td.network_group WHERE ta.id_province = ' . $value->provinceid . ' AND tg.id = ' . $id)->result_object();

                foreach ($district AS $keys => $values) {
                    $address = $this->db->query('SELECT DISTINCT ta.id, ta.address, ta.name FROM tbl_address AS ta LEFT JOIN tbl_device AS td ON td.id_address = ta.id LEFT JOIN tbl_group AS tg ON tg.id = td.network_group WHERE ta.id_district = ' . $values->districtid . ' AND td.network_group = ' . $id)->result_object();

                    foreach ($address AS $keyss => $valuess) {
                        $device = $this->db->query('SELECT DISTINCT td.* FROM tbl_device AS td LEFT JOIN tbl_address AS ta ON ta.id = td.id_address WHERE td.network_group = ' . $id . ' AND ta.id = ' . $valuess->id)->result_object();

                        $address[$keyss]->device = $device;
                    }

                    $district[$keys]->address = $address;
                }

                $province[$key]->district = $district;
            }

            //$sql = "SELECT * FROM tbl_device WHERE network_group = ".$id;
            //$data = $this->db->query($sql)->result_object();

            return $province;
        } else {
            return 0;
        }
    }

    /**
     *
     * @void old
     * 
     */
    public function indexDB($provinceid, $districtid, $status, $s) {
        $sql = '';
        if ($this->session->userdata('level') != 0) {
            $sql = 'SELECT p.name, p.provinceid FROM province AS p INNER JOIN tbl_address AS a ON a.id_province = p.provinceid INNER JOIN address_user AS au ON au.id_address = a.id WHERE au.id_user = ' . $this->session->userdata('id') . ' ';
        } else {
            $sql = 'SELECT p.name, p.provinceid FROM province AS p INNER JOIN tbl_address AS a ON a.id_province = p.provinceid WHERE 1=1 ';
        }
        if (!in_array($provinceid, array('', null))) {
            $sql .= ' AND p.provinceid = ' . $provinceid . ' ';
        }
        $sql .= ' GROUP BY p.provinceid ORDER BY p.name ASC';

        //die(''.$sql);

        $province = $this->db->query($sql)->result_object();

        //echo '<pre>';die(print_r($province));

        foreach ($province AS $el => $le) {
            $sql_1 = '';
            $sql_1 .= 'SELECT d.type, d.`name`, d.districtid FROM tbl_address AS a INNER JOIN district AS d ON a.id_district = d.districtid WHERE a.id_province = ' . $le->provinceid . ' ';
            if (!in_array($districtid, array('', null))) {
                $sql_1 .= ' AND d.districtid = ' . $districtid . ' ';
            }
            $sql_1 .= ' GROUP BY d.districtid';
            $province[$el]->district = $this->db->query($sql_1)->result_object();
            foreach ($province[$el]->district AS $eel => $lle) {
                $sql_2 = '';
                if ($this->session->userdata('level') != 0) {
                    $sql_2 .= 'SELECT ad.id, ad.address, ad.name FROM tbl_address AS ad INNER JOIN address_user AS au ON au.id_address = ad.id WHERE ad.id_district = ' . $lle->districtid . ' AND au.id_user = ' . $this->session->userdata('id') . ' ';
                } else {
                    $sql_2 .= 'SELECT ad.address, ad.id, ad.name FROM tbl_address as ad WHERE ad.id_district = ' . $lle->districtid . ' ';
                }
                if (!in_array($s, array('', null))) {
                    $sql_2 .= ' AND ( ad.address LIKE "%' . $s . '%" OR ad.name LIKE "%' . $s . '%" ) ';
                }
                $province[$el]->district[$eel]->address = $this->db->query($sql_2)->result_object();
                foreach ($province[$el]->district[$eel]->address AS $ele => $lee) {
                    $sql_dv = 'SELECT td.*, uh.url_homepage FROM tbl_device AS td INNER JOIN url_homepage AS uh ON td.type = uh.id WHERE td.id_address = ' . $lee->id . ' ';
                    if (!in_array($status, array('', null))) {
                        if ($status == 1) {
                            $timez = time() - 300;
                            $sql_dv .= ' AND td.time_update > ' . $timez . ' ';
                        } else {
                            if ($status == 2) {
                                $timez = time() - 300;
                                $sql_dv .= ' AND td.time_update < ' . $timez . ' ';
                            }
                        }
                    }


                    $province[$el]->district[$eel]->address[$ele]->device = $this->db->query($sql_dv)->result_object();
                }
            }
        }
        //echo '<pre>';die(print_r($province));
        return $province;
    }

    public function savePlace() {
        $arr = array(
            'id_province' => $this->getParamInt('province'),
            'id_district' => $this->getParamInt('district'),
            'address' => $this->getParamString('address'),
            'created' => date('Y-m-d H:i:s'),
            'lat' => $this->getParamString('lat'),
            'long' => $this->getParamString('long'),
            'name' => $this->getParamString('name')
        );
        $this->db->insert('tbl_address', $arr);
        return true;
    }

    public function getPlaceSelect() {
        $data = $this->db->query('SELECT a.id, p.`name` AS pname, d.`name` AS dname, a.address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province ORDER BY a.address DESC')->result_array();

        $arr = array();
        foreach ($data AS $el => $le) {
            $arr[$le['id']] = $le['address'] . ' - ' . $le['dname'] . ' - ' . $le['pname'];
        }
        return $arr;
    }

    public function saveDevice($fileUpload) {
        $arr = array(
            'id_address' => $this->getParamInt('place'),
            'namedevice' => $this->getParamString('namedevice'),
            'mac' => $this->getParamString('mac'),
            'nasid' => $this->getParamString('nasid'),
            'created' => date('Y-m-d H:i:s'),
            'linkupdate' => $fileUpload,
            'version' => $this->getParamString('version'),
            'active' => $this->getParamInt('active'),
            'type' => $this->getParamInt('types')
        );
        $this->db->insert('tbl_device', $arr);
        return true;
    }

    public function editDevice($id, $fileUpload) {
        $arr = array(
            'id_address' => $this->getParamInt('place'),
            'namedevice' => $this->getParamString('namedevice'),
            'mac' => $this->getParamString('mac'),
            'nasid' => $this->getParamString('nasid'),
            'version' => $this->getParamString('version'),
            'active' => $this->getParamInt('active'),
            'type' => $this->getParamInt('types')
        );
        if ($fileUpload != null) {
            $arr['linkupdate'] = $fileUpload;
        }
        $this->db->update('tbl_device', $arr, "id = " . $id);
        return true;
    }

    public function deleteDeviceInfo($id) {
        return $this->db->delete('tbl_device', "id = " . $id);
    }

    public function deletePlaceInfo($id) {
        return $this->db->delete('tbl_address', "id = " . $id);
    }

    public function editPlace($id) {
        $arr = array(
            'id_province' => $this->getParamInt('province'),
            'id_district' => $this->getParamInt('district'),
            'address' => $this->getParamString('address'),
            'lat' => $this->getParamString('lat'),
            'long' => $this->getParamString('long'),
            'name' => $this->getParamString('name')
        );
        $this->db->update('tbl_address', $arr, "id = " . $id);
        return true;
    }

    public function getPlace($number = 0, $offset = 0) {
        $limit = $offset ? intval($offset) : '0';

        $sql = "SELECT a.id, a.name, p.`name` AS pname, d.`name` AS dname, a.address FROM tbl_address AS a INNER JOIN district AS d ON d.districtid = a.id_district INNER JOIN province AS p ON p.provinceid = a.id_province ORDER BY a.id DESC";

        $count = $this->db->query($sql)->num_rows();

        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $xs = $this->db->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    public function getDevice($number = 0, $offset = 0) {
        $limit = $offset ? intval($offset) : '0';

        $sql = "SELECT dt.`name` AS dname, p.`name` AS pname, d.`nasid`, d.`active`, d.id AS did,a.address, d.namedevice FROM tbl_device AS d INNER JOIN tbl_address AS a ON d.id_address = a.id INNER JOIN province AS p ON a.id_province = p.provinceid INNER JOIN district AS dt ON a.id_district = dt.districtid ORDER BY d.namedevice ASC";

        $count = $this->db->query($sql)->num_rows();

        $xdata = " LIMIT " . $limit . "," . $number . " ";

        $xs = $this->db->query($sql . $xdata)->result_object();
        return array('data' => $xs, 'count' => $count);
    }

    public function getPlaceInfo($id) {
        return $this->db->query('SELECT a.id, a.address, a.id_province, a.id_district, a.lat, a.long, a.name FROM tbl_address AS a WHERE a.id =' . $id)->row_object();
    }

    public function getDeviceInfo($id) {
        return $this->db->query('SELECT * FROM tbl_device WHERE id =' . $id)->row_object();
    }

    public function getListDistrict($id) {
        $data = $this->db->query('SELECT * FROM district WHERE provinceid = ' . $id . ' ORDER BY name ASC')->result_array();

        $arr = array();
        foreach ($data AS $el => $le) {
            $arr[$le['districtid']] = $le['type'] . ' ' . $le['name'];
        }
        return $arr;
    }

    public function updatestatusDB() {
        $this->db->update('tbl_device', array('time_update' => time(), 'warning' => 0), "mac = '" . $this->getParamString('id') . "'");
    }

    public function updatestatusver2DB() {
        $this->db->update('tbl_device', array('time_update' => time(), 'warning' => 0, 'info_network_ap' => $this->getParamString('ifconfig'), 'info_port' => $this->getParamString('info')), "mac = '" . $this->getParamString('id') . "' OR id = " . $this->getParamInt('id_device'));
    }

    public function warningSystemDB() {
        $data = $this->db->query('SELECT id_address, namedevice, mac, warning, id FROM tbl_device WHERE warning < 2 AND ((' . time() . ' - time_update) >= 300) LIMIT 2')->result_object();
        foreach ($data AS $el => $le) {
            //echo 'SELECT u.username, u.mobile,ad.address,u.email,u.fullname FROM `user` AS u INNER JOIN address_user AS au ON au.id_user = u.id INNER JOIN tbl_address AS ad ON ad.id = au.id_address WHERE au.id_address = '.$le->id_address;
            $adata = $this->db->query('SELECT u.username, u.mobile,ad.address,u.email,u.fullname FROM `user` AS u INNER JOIN address_user AS au ON au.id_user = u.id INNER JOIN tbl_address AS ad ON ad.id = au.id_address WHERE au.id_address = ' . $le->id_address)->row_object();
            //$mobile = substr($adata->mobile, 1);
            //echo "<pre>";
            //die(print_r($adata));
            $device = $le->namedevice . $le->mac;
            $addr = $adata->address;
            $content = $device . ', tai dia chi ' . $addr . "khong co internet";

            if ($le->warning < 2) {
                //echo $adata->email;
                /*
                  $sendSMS = json_decode(file_get_contents('http://118.70.109.212:1234/?c='.$content.'&m='.$mobile.'&t_=1423463803'));
                  if($sendSMS->status == 200)
                  {
                  $this->db->update('tbl_device', array('warning' => $le->warning + 1), "id = ".$le->id);
                  echo "-1";
                  }else{
                  echo "-2";
                  }
                 */

                $config = array(
                    'protocol' => 'smtp',
                    'smtp_host' => 'mail.gvtechcom.vn',
                    'smtp_user' => 'info@gvtechcom.vn',
                    'smtp_pass' => 'abcde@123',
                    'mailtype' => 'html'
                );
                $this->load->library('email', $config);
                $body = "Chào bạn " . $adata->fullname . "! <br/>" . $content;
                $this->email->from('info@gvtechcom.vn', 'Cảnh báo hệ thống');
                $this->email->to($adata->email);
                $this->email->subject('Cảnh báo hệ thống');
                $this->email->message($body);
                if ($this->email->send()) {
                    $this->db->update('tbl_device', array('warning' => $le->warning + 1), "id = " . $le->id);
                    echo "-1";
                }
            }
        }
    }

    public function checkupdateDB($id) {
        $data = $this->db->query('SELECT version,linkupdate FROM tbl_device WHERE mac = "' . $id . '"')->row_object();
        return $data->version . '|' . site_url() . 'public/' . $data->linkupdate . '|' . $data->linkupdate;
    }

    public function checkupdatever2DB($id, $id_device) {
        $data = $this->db->query('SELECT version,linkupdate FROM tbl_device WHERE mac = "' . $id . '" OR id = ' . $id_device)->row_object();
        return $data->version . '|' . site_url() . 'public/' . $data->linkupdate . '|' . $data->linkupdate;
    }

    public function checkresetDB($id, $id_device) {
        $data = $this->db->query('SELECT reset_device FROM tbl_device WHERE mac = "' . $id . '" AND id = ' . $id_device)->row_object();
        return $data->reset_device;
    }

    public function getReportDb() {
        $data = $this->db->query('SELECT a.id,a.address,dis.name AS district,prov.name AS province FROM tbl_address AS a LEFT JOIN district AS dis ON dis.districtid = a.id_district LEFT JOIN province AS prov ON prov.provinceid = a.id_province')->result_object();

        foreach ($data AS $key => $value) {
            $query = $this->db->query('SELECT * FROM tbl_device WHERE id_address = ' . $value->id);
            $data[$key]->count_device = $query->num_rows();
            $data[$key]->device = $query->result_object();
        }

        //echo "<pre>"; print_r($data); die();
        return $data;
    }

    public function geturlhomepage() {
        return $this->db->query('SELECT * FROM url_homepage WHERE active = 1')->result_object();
    }

    public function listprovince() {
        return $this->db->query('SELECT * FROM province WHERE 1=1')->result_object();
    }

    public function indexgetdistrict($id) {
        $data = $this->db->query('SELECT * FROM district WHERE provinceid = ' . $id . ' ')->result_object();
        $html = '<option value="" selected="selected">Chọn quận huyện</option>';
        foreach ($data AS $el => $le) {
            $html .= '<option value="' . $le->districtid . '" >' . $le->name . '</option>';
        }
        return $html;
    }

    public function indexlistdistrict($pid, $did) {
        $data = $this->db->query('SELECT * FROM district WHERE provinceid = ' . $pid . ' ')->result_object();
        $html = '<option value="">Chọn quận huyện</option>';
        foreach ($data AS $el => $le) {
            if ($le->districtid == $did) {
                $select = 'selected="selected"';
            } else {
                $select = '';
            }
            $html .= '<option value="' . $le->districtid . '" ' . $select . ' >' . $le->name . '</option>';
        }
        return $html;
    }

    public function devicedetail($id) {
        $data = $this->db->query('SELECT td.*, ta.address FROM tbl_device as td inner join tbl_address as ta on ta.id = td.id_address WHERE td.id = ' . $id . ' ')->row_object();
        $data->comment = $this->db->query('SELECT * FROM comment WHERE device_id = ' . $id . ' ORDER BY created desc ')->result_object();
        return $data;
    }

    public function addCommentDb($id, $content) {
        $arr = array(
            'device_id' => $id,
            'content' => $content,
            'admin_id' => $this->session->userdata('id'),
            'created' => date('Y/m/d H:i:s'),
            'active' => 1
        );
        $this->db->insert('comment', $arr);
        return 200;
    }

    public function ajaxupdatecomment() {
        $val = $this->getParamString('val');
        $val1 = explode('-', $val);
        $id = $val1[0];
        $active = $val1[1];
        if ($active == 1) {
            $active = 2;
        } else {
            $active = 1;
        }
        $arr = array('active' => $active);
        $this->db->update('comment', $arr, 'id = ' . $id . ' ');
        //die('111');
        return 200;
    }

}
